﻿using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    public class VideoService : BaseService
    {  /// <summary>
        /// 获取视频列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string name, int videoCategoryId, string language, int pageValue, int rowsValue)
        {
            var parOne = new[]
                             {
                                 new SQLiteParameter("@CoName", DbType.String, 255),
                                 new SQLiteParameter("@CoVideoCategoryId", DbType.Int32),
                                 new SQLiteParameter("@CoLanguage", DbType.String, 255)
                                   };
            parOne[0].Value = name.SqliteLike();
            parOne[1].Value = videoCategoryId;
            parOne[2].Value = language.SqliteLike();
            var strWhere = new StringBuilder();
            strWhere.Append(" where A.CoName like '%'||@CoName||'%' escape '\\' and A.CoLanguage like '%'||@CoLanguage||'%' escape '\\' ");
            if (videoCategoryId != 0)
            {
                strWhere.Append(" and A.CoVideoCategoryId=@CoVideoCategoryId");
            }
            var sqlOne = new StringBuilder();
            sqlOne.Append("select count(*) from TbVideo as A ");
            sqlOne.Append(strWhere);
            int total = DbHelperSqLite.GetSingle(sqlOne.ToString(), parOne).SafeToInt();

            var parTwo = new[]
                           {
                               new SQLiteParameter("@startIndex", DbType.Int32),
                               new SQLiteParameter("@endIndex", DbType.Int32),   
                               new SQLiteParameter("@CoName", DbType.String, 255),
                               new SQLiteParameter("@CoVideoCategoryId", DbType.Int32),
                               new SQLiteParameter("@CoLanguage", DbType.String, 255)
                           };
            parTwo[0].Value = pageValue > 0 ? (pageValue - 1) * rowsValue : 0;
            parTwo[1].Value = rowsValue;
            parTwo[2].Value = name;
            parTwo[3].Value = videoCategoryId;
            parTwo[4].Value = language;
            var sqlTwo = new StringBuilder();
            sqlTwo.Append("select A.CoVideoId,A.CoNormalImage,A.CoSmallImage,A.CoBigImage,A.CoName,A.CoVideoCategoryId,A.CoIsRecommend,A.CoIsPopular,A.CoIsTop,A.CoViews,A.CoAuthor,A.CoDate,A.CoIsReserved,A.CoReservedUrl,A.CoLanguage, B.CoName as CoCategoryName from TbVideo as A left join TbVideoCategory as B on  A.CoVideoCategoryId=B.CoVideoCategoryId ");
            sqlTwo.Append(strWhere);
            sqlTwo.Append(" order by covideoid ");
            sqlTwo.Append(" LIMIT @endIndex OFFSET @startIndex ");
            var ds = DbHelperSqLite.Query(sqlTwo.ToString(), parTwo);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total
            };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperSqLite.Query("select *  from TbVideo  where CoVideoId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }
        public int Add(int videoCategoryId, string name, string content, string summary, string author, string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved, bool isPopular, bool isTop, HttpPostedFileBase filedata)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "视频名称");
            Check.Argument.IsNotOutOfLength(summary, 252, "视频简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(language, 20, "视频语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            if (filedata.ContentLength <= 0)
            {
                throw new ArgumentException("封面图片不能为空");
            }
            var ext = Path.GetExtension(filedata.FileName);
            var path = HttpContext.Current.Server.MapPath("~/Upload/Video/Image/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            var fileName = Guid.NewGuid() + ext;
            var thumFileName = "thum_" + fileName;
            var filePath = Path.Combine(path, fileName);
            var thumFilePath = Path.Combine(path, thumFileName);
            filedata.SaveAs(filePath);
            ImageExtension.ToThumbnail(filePath, thumFilePath, 185, 165);
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("insert into TbVideo(");
            strSqlOne.Append("CoName,CoContent,CoSummary,CoReservedUrl,CoAuthor,CoVideoCategoryId,CoViews,CoLanguage,CoDate,CoIsReserved,CoIsPopular,CoIsTop,CoIsRecommend,CoCategoryPath,CoNormalImage,CoSmallImage,CoBigImage");
            strSqlOne.Append(")");
            strSqlOne.Append(" values (");
            strSqlOne.Append("@CoName,@CoContent,@CoSummary,@CoReservedUrl,@CoAuthor,@CoVideoCategoryId,@CoViews,@CoLanguage,@CoDate,@CoIsReserved,@CoIsPopular,@CoIsTop,@CoIsRecommend,@CoCategoryPath,@CoNormalImage,@CoSmallImage,@CoBigImage");
            strSqlOne.Append(");");
            strSqlOne.Append("select last_insert_rowid() from TbVideo;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoVideoCategoryId", DbType.Int32),
                    new SQLiteParameter("@CoCategoryPath", DbType.String),
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoContent", DbType.String),
                    new SQLiteParameter("@CoSummary", DbType.String),
                    new SQLiteParameter("@CoReservedUrl", DbType.String, 255),
                    new SQLiteParameter("@CoAuthor", DbType.String, 255),
                    new SQLiteParameter("@CoLanguage", DbType.String, 10),
                    new SQLiteParameter("@CoDate", DbType.DateTime),
                    new SQLiteParameter("@CoIsReserved", DbType.Boolean),
                    new SQLiteParameter("@CoIsPopular", DbType.Boolean),
                    new SQLiteParameter("@CoIsTop", DbType.Boolean),
                    new SQLiteParameter("@CoIsRecommend", DbType.Boolean),
                    new SQLiteParameter("@CoViews", DbType.Int32),
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255)
                };
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from TbVideoCategory where CoVideoCategoryId={0}".FormatWith(videoCategoryId)))
            {
                if (sdr.Read())
                {
                    parametersOne[0].Value = videoCategoryId;
                    parametersOne[1].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    parametersOne[0].Value = DBNull.Value;
                    parametersOne[1].Value = "";
                }
            }
            parametersOne[2].Value = name;
            parametersOne[3].Value = content;
            parametersOne[4].Value = summary;
            parametersOne[5].Value = reservedUrl;
            parametersOne[6].Value = author;
            parametersOne[7].Value = language;
            parametersOne[8].Value = date;
            parametersOne[9].Value = isReserved;
            parametersOne[10].Value = isPopular;
            parametersOne[11].Value = isTop;
            parametersOne[12].Value = isRecommend;
            parametersOne[13].Value = 0;
            parametersOne[14].Value = fileName;
            parametersOne[15].Value = thumFileName;
            parametersOne[16].Value = fileName;
            var obj = DbHelperSqLite.GetSingle(strSqlOne.ToString(), parametersOne).SafeToInt32();
            return obj;
        }
        /// <summary>
        /// 编辑视频
        /// </summary>
        /// <param name="id">视频ID</param>
        /// <param name="videoCategoryId">视频分类ID</param>
        /// <param name="name">视频名称</param>
        /// <param name="content">视频内容</param>
        /// <param name="summary">视频简介</param>
        /// <param name="author">作者</param>
        /// <param name="language">语言</param>
        /// <param name="reservedUrl">转载地址</param>
        /// <param name="date">日期</param>
        /// <param name="isRecommend">推荐</param>
        /// <param name="isReserved"></param>
        /// <param name="isPopular"></param>
        /// <param name="isTop">置顶</param>
        /// <param name="filedata">文件FileData</param>
        /// <returns></returns>
        public int Edit(int id, int videoCategoryId, string name, string content, string summary, string author, string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved, bool isPopular, bool isTop, HttpPostedFileBase filedata)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "视频名称");
            Check.Argument.IsNotOutOfLength(summary, 252, "视频简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(language, 20, "视频语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("update TbVideo set ");
            strSqlOne.Append("CoVideoCategoryId=@CoVideoCategoryId");
            strSqlOne.Append(",");
            strSqlOne.Append("CoCategoryPath=@CoCategoryPath");
            strSqlOne.Append(",");
            strSqlOne.Append("CoName=@CoName");
            strSqlOne.Append(",");
            strSqlOne.Append("CoContent=@CoContent");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSummary=@CoSummary");
            strSqlOne.Append(",");
            strSqlOne.Append("CoReservedUrl=@CoReservedUrl");
            strSqlOne.Append(",");
            strSqlOne.Append("CoAuthor=@CoAuthor");
            strSqlOne.Append(",");
            strSqlOne.Append("CoLanguage=@CoLanguage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoDate=@CoDate");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIsReserved=@CoIsReserved");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIsPopular=@CoIsPopular");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIsTop=@CoIsTop");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIsRecommend=@CoIsRecommend");
            if (filedata.ContentLength > 0)
            {
                strSqlOne.Append(",");
                strSqlOne.Append("CoNormalImage=@CoNormalImage");
                strSqlOne.Append(",");
                strSqlOne.Append("CoSmallImage=@CoSmallImage");
                strSqlOne.Append(",");
                strSqlOne.Append("CoBigImage=@CoBigImage");
            }
            strSqlOne.Append(" where CoVideoId=@id;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoVideoCategoryId", DbType.Int32),
                    new SQLiteParameter("@CoCategoryPath", DbType.String),
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoContent", DbType.String),
                    new SQLiteParameter("@CoSummary", DbType.String),
                    new SQLiteParameter("@CoReservedUrl", DbType.String, 255),
                    new SQLiteParameter("@CoAuthor", DbType.String, 255),
                    new SQLiteParameter("@CoLanguage", DbType.String, 10),
                    new SQLiteParameter("@CoDate", DbType.DateTime),
                    new SQLiteParameter("@CoIsReserved", DbType.Boolean),
                    new SQLiteParameter("@CoIsPopular", DbType.Boolean),
                    new SQLiteParameter("@CoIsTop", DbType.Boolean),
                    new SQLiteParameter("@CoIsRecommend", DbType.Boolean),  
                    new SQLiteParameter("@id", DbType.Int32),
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255)
                };
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from TbVideoCategory where CoVideoCategoryId={0}".FormatWith(videoCategoryId)))
            {
                if (sdr.Read())
                {
                    parametersOne[0].Value = videoCategoryId;
                    parametersOne[1].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    parametersOne[0].Value = DBNull.Value;
                    parametersOne[1].Value = "";
                }
            }
            parametersOne[2].Value = name;
            parametersOne[3].Value = content;
            parametersOne[4].Value = summary;
            parametersOne[5].Value = reservedUrl;
            parametersOne[6].Value = author;
            parametersOne[7].Value = language;
            parametersOne[8].Value = date;
            parametersOne[9].Value = isReserved;
            parametersOne[10].Value = isPopular;
            parametersOne[11].Value = isTop;
            parametersOne[12].Value = isRecommend;
            parametersOne[13].Value = id;
            if (filedata.ContentLength > 0)
            {
                var ext = Path.GetExtension(filedata.FileName);
                var path = HttpContext.Current.Server.MapPath("~/Upload/Video/Image/");
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                var fileName = Guid.NewGuid() + ext;
                var thumFileName = "thum_" + fileName;
                var filePath = Path.Combine(path, fileName);
                var thumFilePath = Path.Combine(path, thumFileName);
                filedata.SaveAs(filePath);
                ImageExtension.ToThumbnail(filePath, thumFilePath, 185, 165);
                var ds = DbHelperSqLite.Query("select * from TbVideo where CoVideoId={0}".FormatWith(id));
                foreach (DataRow item in ds.Tables["ds"].Rows)
                {
                    FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
                }
                parametersOne[14].Value = fileName;
                parametersOne[15].Value = thumFileName;
                parametersOne[16].Value = fileName;
            }
            var obj = DbHelperSqLite.ExecuteSql(strSqlOne.ToString(), parametersOne).SafeToInt();
            return obj;

        }
        /// <summary>
        /// 删除视频
        /// </summary>
        /// <param name="id">视频ID</param>
        public void Delete(int id)
        {
            DbHelperSqLite.ExecuteSql("delete from TbVideo where CoVideoId={0};".FormatWith(id));
        }
    }
}
